// Finds duplicate field values and sends an e-mail reporting on all the duplciates.
// Use on fields marked as External IDs that are not marked as unique
global class FindDuplicateFieldValues implements Database.Batchable<sObject>, Database.Stateful{

    // Run this job using an anonymous call. Example:
    // id batchinstanceid = database.executeBatch(new FindDuplicateFieldValues('Account','Ingenio_UserID__c', -1, 'Dev Sandbox', 'cs1')); 
    
    global List<Match> Dupes = new List<Match>();       
    global Match PreviousValue= null;
    global integer TotalCount = 0;
    
    private final string FieldName;
    private final string ObjectName;
    private final integer MaxRowsToQuery;
    private final string Decription;
    private final string SandboxUrlPrefix;
    private boolean MergeObjects;
    
    private class Match
    {
        public Match(object value, sObject obj)
        {
            this.Value = value;
            AddMatch(obj);
        }
        public void AddMatch(sObject obj)
        {
            Objects.add(obj);            
        }
        public object Value;
        public List<sObject> Objects = new List<sObject>();
        public integer NumberOfMatches()
        {
            return Objects.size();
        }
    }
    
    public boolean MergeDuplicateObjects
    {
        set
        {
            this.MergeObjects = value;
        }
    }
    global FindDuplicateFieldValues(string objectName, string fieldName, integer maxRowsToQuery, string decription, string sandboxUrlPrefix)
    {
        this.ObjectName = objectName;
        this.FieldName = fieldName;
        this.MaxRowsToQuery = maxRowsToQuery;
        this.Decription = decription;
        this.SandboxUrlPrefix = sandboxUrlPrefix;
        MergeObjects = false;
    }
    
    global Database.Querylocator start(Database.BatchableContext bc){
        
        string query = 'Select ' + FieldName + ', Name, CreatedDate, CreatedBy.Name, RecordType.Name From ' + ObjectName + ' where ' + FieldName + '<> null order by ' + FieldName + ', CreatedDate';

        if(maxRowsToQuery > 0)
            query += ' LIMIT ' + MaxRowsToQuery;
            
        return Database.getQueryLocator(query);
    }
            
    global void execute(Database.BatchableContext bc, List<sObject> scope){

        object currentValue;
        
        for(sObject s : scope){            
            totalCount++;
            currentValue= s.Get(FieldName);
            if(PreviousValue != null && currentValue == PreviousValue.Value)
            {
                // Match
                PreviousValue.AddMatch(s) ;
                if(PreviousValue.NumberOfMatches() == 2)
                    Dupes.add(PreviousValue);
                
                if(MergeObjects)
                {
                    // Merge the 1st machted object with this one
                    if(ObjectName == 'Account')
                    {
                        Account o1 = new Account(ID= PreviousValue.Objects[0].ID);
                        Account o2 = (Account)s;
                        merge o1 o2;
                    }
                    if(ObjectName == 'Contact')
                    {
                        Contact o1 = new Contact(ID = PreviousValue.Objects[0].ID);
                        Contact o2 = (Contact)s;
                        merge o1 o2;
                    }
                }
            }
            else
                PreviousValue = new Match(currentValue, s);
        }
    }
    
    global void finish(Database.BatchableContext bc){
        
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
    
        mail.setToAddresses(new String[] {'salesforcedev@atti.com'});
        //mail.setToAddresses(new String[] {'arhemtulla@atti.com'});

        mail.setReplyTo('arhemtulla@atti.com');
        mail.setSenderDisplayName('Batch Processing');
        mail.setSubject('Find Duplicate Field Values Completed.');
                
        string body = '<FONT face="Calibri, Arial" size="3">Find Duplicate Field Values - Batch Process has completed.<BR>Description: '+ decription+ '<BR>Job ID: ' + bc.getJobID() + '<BR>Object: '+ ObjectName +'<BR>Field Name: ' +FieldName + '<BR>Duplicates Found: ' + dupes.size() + '<BR> Total rows scanned: ' + totalCount;
        if(MaxRowsToQuery == totalCount)
            body += '. <B>Not all rows were scanned. Results may be incomplete</B>';
            
        body += '.<BR><BR>Duplicates: <BR><TABLE>';        
        body += '<TR><TD>Row</TD><TD>Num Matches</TD><TD>Value</TD><TD>Name</TD><TD>Creation Date</TD><TD>Created By</TD><TD>Record Type</TD></TR>';
        
        for(integer i=0; i < Dupes.size(); i++)
        {
            integer numMatches = Dupes[i].NumberOfMatches();
            string spanCol = '<TD rowspan=' + numMatches + '>';
            string bgColor = '';
            if(math.mod(i,2) == 0)
                bgColor = ' BGCOLOR="#CCCC99" ';
                
            body += '<TR' + bgColor  + '>' + spanCol + (i + 1) + '</TD>' + spanCol + numMatches  + '</TD>' + spanCol + Dupes[i].Value + '</TD>';
            for(integer j=0; j < numMatches; j++)
            {
                sObject s = Dupes[i].Objects[j];
                DateTime creationDate = (DateTime)s.Get('CreatedDate');
                string name = (string)s.Get('Name');
                string createdBy = (string)s.getSObject('CreatedBy').get('Name');
                RecordType rt = (RecordType)s.getSObject('RecordType');
                string recordTypeName = (null != rt) ? rt.Name : '';
                ID id = s.ID;
                if(j!=0) body += '<TR' + bgColor + '>';
                body += '<TD><A href="https://' + SandboxUrlPrefix +'.salesforce.com/' + id + '">' + name + '</A></TD><TD>' + creationDate + '</TD><TD>'+ createdBy +'</TD><TD>' + recordTypeName + '</TD></TR>';
            }
        }
        body += '</TABLE></FONT>';
        
        mail.setHtmlBody(body);
    
        Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
    }
}